// INSE6260-ATM Simulator Project

/**
 * Model package contains core code of ATMS
 */
package com.inse6260.atms.model;

// import statements
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl;   // Sun's JdbcRowSet implementation
import java.util.ArrayList;
import java.util.Date;
import java.sql.Timestamp;

/**
 * An instance of this class provides services to interact with the bank database.
 * @author dimitri.tiago
 * @version 1.0.0
 */
public class BankDatabase
{
    private JdbcRowSet rowSet;  // row set object to interact with database

    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.intersys.jdbc.CacheDriver";
    static final String DATABASE_URL = "jdbc:Cache://localhost:60601/INSE6260";
    static final String USERNAME = "_SYSTEM";
    static final String PASSWORD = "bsobso";

    /**
     * default no-argument constructor initializes database connection
     */
    public BankDatabase()
    {
        try
        {
            // load driver class
            Class.forName(JDBC_DRIVER);     
            
            // initialize jdbcRowSet
            rowSet = new JdbcRowSetImpl();
            rowSet.setUrl(DATABASE_URL);
            rowSet.setUsername(USERNAME);
            rowSet.setPassword(PASSWORD);
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
        catch (ClassNotFoundException classNotFound)
        {
            classNotFound.printStackTrace();
            System.exit(1);
        }
    }
    
    /**
     * Returns bank client details. Also indicates if bank client is authorized (i.e. pin and card number exist in database)
     * @param cardNumber bank client card number.
     * @param pin bank client pin
     * @return <code>String</code> object array containing bank client name and card number. Returns null if bank client not in db
     * (i.e. not authorized).
     */
    public void authorizeBankClient(BankClient currentBankClient)
    {
        // obtain client details
        try
        {
            // get bank client credentials
            int cardNumber = currentBankClient.getCardNumber();
            int pin = currentBankClient.getPin();

            // execute sql query
            String sqlQuery = String.format("SELECT cardNumber, dailyTransactionLimit, blocked, maxOneTimeWithAmt FROM atms.BankClient WHERE cardNumber = %d AND pin = %d",
                    cardNumber, pin);
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            // if validated (i.e. bank client matches card number and pin)
            if (rowSet.next())
            {
                // authorize BankClient object unless card number is blocked
                boolean blocked         = rowSet.getBoolean("blocked");
                float dailyTransLimit   = Float.parseFloat(rowSet.getString("dailyTransactionLimit"));
                float maxOneTimeWithAmt = Float.parseFloat(rowSet.getString("maxOneTimeWithAmt"));

                if (blocked == false)
                {
                    currentBankClient.setDailyTransLimit(dailyTransLimit);
                    currentBankClient.setAuthorized(true);
                    currentBankClient.setMaxOneTimeWithAmt(maxOneTimeWithAmt);
                }

                currentBankClient.setBlocked(blocked);
            }
            else
            {
                // increment no of login retries (there is no reason to increment after 4 tries).
                if (currentBankClient.getNoOfLoginRetries() < 5)
                {
                        currentBankClient.incNoOfLoginRetries();
                }
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Block BankClient card number
     */
    public void blockBankClient(BankClient currentBankClient)
    {
        // block in database
        int cardNumber  = currentBankClient.getCardNumber();
        int pin         = currentBankClient.getPin();
        String sqlQuery = "";
        
        // block bank client
        try
        {
            sqlQuery = String.format("SELECT * FROM atms.BankClient "     // get row
                    + "WHERE cardNumber = %d", cardNumber);

            rowSet.setCommand(sqlQuery);
            rowSet.execute();
           
            if (rowSet.next())
            {
                rowSet.updateBoolean("blocked", true);                    // update row
                rowSet.updateRow();
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }
 
    /**
     * Returns true if the client card number exists in the system
     * @param currentBankClient current bank client
     * @return true if bank client exists in database.
     */
    public boolean cardNoExists(BankClient currentBankClient)
    {
        // determine if card number exists in database
        int cardNumber  = currentBankClient.getCardNumber();
        String sqlQuery = String.format("SELECT cardNumber FROM atms.BankClient WHERE cardNumber = %d", cardNumber);

        // execute sql query
        try
        {
            rowSet.setCommand(sqlQuery);
            rowSet.execute();
        
            // determine if card number exists
            if (rowSet.next())
            {
                // card number exists
                return true;
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }

        // card number does not exist in database
        return false;
    }

    public BankAccount[] getAccounts(int cardNumber)
    {
        // local variable declaration
        ArrayList<BankAccount> accList = new ArrayList<BankAccount>();
        String sqlQuery  = "";

        // obtain account balance
        try
        {
            // obtain current account balance
            sqlQuery = "SELECT * FROM atms.BankAccount WHERE cardNumber = " + cardNumber;
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            while (rowSet.next())
            {
                int accNumber    = Integer.parseInt(rowSet.getString("accountNumber"));
                String accType   = rowSet.getString("accountType");
                float accBalance = Float.parseFloat(rowSet.getString("balance"));

                BankAccount account = new BankAccount(accNumber, accType, accBalance);

                accList.add(account);
            }

            
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }

        return accList.toArray(new BankAccount[0]);
    }

    public TransactionLineItem[] getTransLineItems(int accountNumber)
    {
        // local variable declaration
        ArrayList<TransactionLineItem> transLineItemList = new ArrayList<TransactionLineItem>();

        // obtain account balance
        try
        {
            // obtain current account balance
            String sqlQuery  = "";
            java.util.Date today = new java.util.Date();
            java.sql.Timestamp currTimestamp = new Timestamp(today.getTime());
            sqlQuery = "SELECT * FROM atms.TransactionLineItem WHERE accountNumber = " + accountNumber + " AND datediff('dd', transactionDate, '" + currTimestamp + "'  ) < 30";

            rowSet.setMaxRows(30);
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            while (rowSet.next())
            {
                int id           = rowSet.getInt("ID");
                int accNum       = rowSet.getInt("accountNumber");
                String transType = rowSet.getString("transactionType");
                float amt        = rowSet.getFloat("transactionAmount");
                Timestamp date   = rowSet.getTimestamp("transactionDate");

                TransactionLineItem transLineItem = new TransactionLineItem(id, accNum, transType, amt, date);
                transLineItemList.add(transLineItem);
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }

        return transLineItemList.toArray(new TransactionLineItem[0]);
    }

    /**
     * Returns account balance.
     * @param accountNumber BankClient account number
     * @return account balance
     */
    public float getAccountBalance(int accountNumber)
    {
        // local variable declaration
        float accBalance = 0;
        String sqlQuery  = "";

        // obtain account balance
        try
        {
            // obtain current account balance
            sqlQuery = "SELECT balance FROM atms.BankAccount WHERE accountNumber = " + accountNumber;
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            if (rowSet.next())
            {
                accBalance = Float.parseFloat(rowSet.getString("balance"));
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }

        return accBalance;
    }

    /**
     * Credit bank account with amount
     * @param accountNumber BankClient account number
     * @param amount credit amount
     */
    public void credit(int accountNumber, float creditAmount)
    {
        // loacl variable declarations
        float currentAccBalance = 0;
        String sqlQuery = "";

        // obtain current amount and add credit
        try
        {
            // obtain current account balance
            sqlQuery = "SELECT balance FROM atms.BankAccount WHERE accountNumber = " + accountNumber;
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            if (rowSet.next())
            {
                currentAccBalance = Float.parseFloat(rowSet.getString("balance"));
            
            // add credit
            currentAccBalance += creditAmount;

            // deposit new current balance
            rowSet.updateFloat("balance", currentAccBalance);
            rowSet.updateRow();

            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Debit BankClient account with amount
     * @param accountNumber BankClient card number
     * @param debitAmount debit amount
     */
    public void debit(int accountNumber, float debitAmount)
    {
        // loacl variable declarations
        float currentAccBalance = 0;
        String sqlQuery = "";

        // obtain current amount and add credit
        try
        {
            // obtain current account balance
            sqlQuery = "SELECT * FROM atms.BankAccount WHERE accountNumber = " + accountNumber;
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            if (rowSet.next())
            {
                currentAccBalance = Float.parseFloat(rowSet.getString("balance"));

                // add credit
                currentAccBalance -= debitAmount;

                // deposit new current balance
                rowSet.updateFloat("balance", currentAccBalance);
                rowSet.updateRow();
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Debit BankClient daily transaction limit
     * @param amount cash amount to debit.
     */
    public void debitDailyTransLimit(BankClient currentBankClient, float amount)
    {
        // local variables
        float newDailyTransLimit = 0;
        float oldDailyTransLimit = 0;
        int   cardNumber         = currentBankClient.getCardNumber();
        int   pin                = currentBankClient.getPin();

        // debit daily transaction limit
        oldDailyTransLimit = currentBankClient.getDailyTransLimit();
        newDailyTransLimit = oldDailyTransLimit - amount;
        currentBankClient.setDailyTransLimit(newDailyTransLimit);                                   // update bank client object

        try
        {
            // update bank client in database
            String sqlQuery = String.format("SELECT * FROM atms.BankClient  "
                + "WHERE cardNumber = %d AND pin = %d", cardNumber, pin);
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            if (rowSet.next())
            {
                // update daily transaction limit
                rowSet.updateFloat("dailyTransactionLimit", newDailyTransLimit);
                rowSet.updateRow();
            }
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }

    public void insertTransLineItem(int accountNumber, String transactionType, float transactionAmount)
    {
        // local variables
        try
        {
            // insert new transaction line item row
            String sqlQuery = String.format("SELECT accountNumber, transactionType, transactionAmount, transactionDate FROM atms.TransactionLineItem");
            rowSet.setMaxRows(1);
            rowSet.setCommand(sqlQuery);
            rowSet.execute();

            
            java.util.Date today = new java.util.Date();
            java.sql.Timestamp currTimestamp = new Timestamp(today.getTime());

            // update daily transaction limit
            rowSet.moveToInsertRow();
            rowSet.updateInt("accountNumber", accountNumber);
            rowSet.updateString("transactionType", transactionType);
            rowSet.updateFloat("transactionAmount", transactionAmount);
            rowSet.updateTimestamp("transactionDate", currTimestamp);
            rowSet.insertRow();
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Closes bank database connection
     */
    public void closeConnection()
    {
        try
        {
            rowSet.close();
        }
        catch (SQLException sqlException)
        {
            sqlException.printStackTrace();
        }
    }
}
